package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import util.JDBCUtil;

import domain.Comment;
import ex.DatabaseException;

public class CommentDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	
	private static final String SELECT_COMMENT_BY_UIDR="select * from comments where uidR= ? ";
	private static final String SELECT_COMMENT_BY_MID="select * from comments where mid= ? ";
	private static final String UPDATE_COMMENT="update comments set comment = ? where cid = ?";
	private static final String CREATE_COMMENT="insert into comments  (uidS, uidR, mid, comment) values (?,?,?,?) ";
	private static final String INCREMENT_FLAG="update comments set flag = flag + 1 where cid = ?";
	private static final String DELETE_COMMENT="delete comment where cid = ?";

	//Selection of the Comments using the User ID in the Comment table
	public LinkedList<Comment> selectCommentsByUidR(long uidR) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		long uidS = -1;
		long mid = -1;
		long cid = -1;
		String cmnt = null;
		Timestamp timestamp = null;
		long flag = -1;
		LinkedList<Comment> comments = new LinkedList<Comment>();
		//get the columns of Comment table according to the user id of the receiver
		try {
			stmt = conn.prepareStatement(SELECT_COMMENT_BY_UIDR);
			stmt.setLong(1, uidR);
			rs = stmt.executeQuery();
			if (rs.next()) {
				uidS = rs.getLong("uidS");
				mid = rs.getLong("mid");
				cmnt = rs.getString("comment");
				cid = rs.getLong("cid");
				timestamp = rs.getTimestamp("timestamp");
				flag = rs.getLong("flag");
				comments.add(new Comment(uidS,uidR,mid,cmnt,timestamp,flag,cid));
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving comments! "	+ e.getMessage());
		}			
		
		return comments;		
	}
	
	public LinkedList<Comment> selectCommentsByMid(long mid) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		long uidS = -1;
		long uidR = -1;
		long cid = -1;
		String cmnt = null;
		Timestamp timestamp = null;
		long flag = -1;
		LinkedList<Comment> comments = new LinkedList<Comment>();
		//get the columns of Comment table according to the movie id
		try {
			stmt = conn.prepareStatement(SELECT_COMMENT_BY_MID);
			stmt.setLong(1, uidR);
			rs = stmt.executeQuery();
			if (rs.next()) {
				uidS = rs.getLong("uidS");
				uidR = rs.getLong("uidR");
				cid = rs.getLong("cid");
				cmnt = rs.getString("comment");
				timestamp = rs.getTimestamp("timestamp");
				flag = rs.getLong("flag");
				comments.add(new Comment(uidS,uidR,mid,cmnt,timestamp,flag, cid));
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving comments! "	+ e.getMessage());
		}			
		
		return comments;		
	}
	
	//Insertion of a new Comment to the database
	public void insertComment(Comment cmt) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(CREATE_COMMENT);
			stmt.setLong(1, cmt.getUidS());
			stmt.setLong(2, cmt.getUidR());
			stmt.setLong(3, cmt.getMid());
			stmt.setString(4, cmt.getComment());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with inserting comment! "	+ e.getMessage());
		}			
		
	}
	
	//Increasing the comment flag
	public void incrementCommentFlag(Comment cmt) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(INCREMENT_FLAG);
			stmt.setLong(1, cmt.getCid());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with incrementing comment flag! "	+ e.getMessage());
		}				
	}
	
	//Removal of a Comment from the database
	public void deleteComment(Comment cmt) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(DELETE_COMMENT);
			stmt.setLong(1, cmt.getCid());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with deleting comment! "	+ e.getMessage());
		}				
	}
	
	//Editing the Comment fields
	public void updateComment(Comment cmt) throws DatabaseException{
		conn = JDBCUtil.getConnection();
		try {
			stmt = conn.prepareStatement(UPDATE_COMMENT);
			stmt.setString(1, cmt.getComment());
			stmt.setLong(2, cmt.getCid());
			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		}catch (SQLException e) {
			throw new DatabaseException("Problem with editing comment! "	+ e.getMessage());
		}				
	}
	
	
}
